À seguir temos um modelo de dados comumente utilizado em lojas virtuais (e-commerce). Esse modelo é bastante adotado em sistemas reais e também é excelente para fins didáticos, pois cobre os principais processos: cadastro, catálogo, pedidos e pagamentos.

---

Visão Geral do Modelo

O sistema de loja virtual é composto basicamente por cinco grandes áreas:

1. Clientes e Endereços

2. Produtos e Categorias

3. Pedidos

4. Pagamentos

5. Entrega

---

Entidades (tabelas) e Atributos (colunas)

- Cliente

Representa o usuário comprador da loja.

• id_cliente (PK)

• nome

• email

• senha

• cpf

• data_cadastro

• telefone

Obs: Um cliente pode ter vários endereços e vários pedidos.

---

- Endereço

Usado para entrega e/ou cobrança.

• id_endereco (PK)

• logradouro

• numero

• complemento

• bairro

• cidade

• estado

• cep

• tipo (entrega, cobrança)

• id_cliente (FK)

Obs: Optamos por registrar os endereços na tabela endereços para que os clientes possam registrar mais de um endereço, como o endereço de correspondência e o endereço de entrega. 

---

- Categoria

Organiza os produtos.

• id_categoria (PK)

• nome

• descricao

Obs: Uma categoria pode possuir vários produtos.

---

- Produto

Itens vendidos na loja.

• id_produto (PK)

• nome

• descricao

• preco

• estoque

• status (ativo/inativo)

• id_categoria (FK)

---

- Pedido

Representa uma compra realizada pelo cliente.

• id_pedido (PK)

• data_pedido

• status (aberto, pago, enviado, cancelado)

• valor_total

• id_cliente (FK)

• id_endereco (FK)

Obs: Um cliente pode fazer vários pedidos.

---

- ItemPedido

Tabela associativa entre Pedido e Produto.

• id_item_pedido (PK)

• quantidade

• preco_unitario

• id_pedido (FK)

• id_produto (FK)

Obs: Um pedido possui vários produtos e um produto pode estar em vários pedidos. A coluna preco_unitario, receberá um valor igual ao da coluna preco da tabela produto, caso não seja aplicado nenhum desconto na venda. Caso seja aplicado algum desconto, o valor da coluna preco_unitario será gravada com o valor final após o desconto. Optamos por não registrar o valor do percentual de desconto aplicado, pois este valor pode ser obtido dividindo o preco_unitario pelo preco e multiplicando por 100% ((preco_unitario / preco) * 100).

---

- Pagamento

Registra informações financeiras do pedido.

• id_pagamento (PK)

• tipo_pagamento (cartão, pix, boleto)

• status_pagamento (pendente, aprovado, recusado)

• valor

• data_pagamento

• id_pedido (FK)

Obs: Normalmente um pedido possui um pagamento (mas o modelo permite mais de um, se necessário).

---

- Entrega

Controle do envio do pedido.

• id_entrega (PK)

• transportadora

• codigo_rastreio

• status_entrega

• data_envio

• data_entrega

• id_pedido (FK)

---

Relacionamentos (Resumo)

• Cliente 1:N Endereço

• Cliente 1:N Pedido

• Categoria 1:N Produto

• Pedido 1:N ItemPedido

• Produto 1:N ItemPedido

• Pedido 1:1 Pagamento

• Pedido 1:1 Entrega

---

Diagrama Entidade Relacionamento

---

Script de Criação do Banco de Dados

CREATE DATABASE loja_virtual;

USE loja_virtual;

CREATE TABLE cliente (
    id_cliente INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    senha VARCHAR(100) NOT NULL,
    cpf VARCHAR(14) NOT NULL UNIQUE,
    telefone VARCHAR(20),
    data_cadastro DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE endereco (
    id_endereco INT AUTO_INCREMENT PRIMARY KEY,
    logradouro VARCHAR(150),
    numero VARCHAR(10),
    complemento VARCHAR(50),
    bairro VARCHAR(50),
    cidade VARCHAR(50),
    estado CHAR(2),
    cep VARCHAR(10),
    tipo ENUM('entrega','cobranca'),
    id_cliente INT,
    FOREIGN KEY (id_cliente) REFERENCES cliente(id_cliente)
);

CREATE TABLE categoria (
    id_categoria INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(50) NOT NULL,
    descricao VARCHAR(200)
);

CREATE TABLE produto (
    id_produto INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    descricao VARCHAR(200),
    preco DECIMAL(10,2) NOT NULL,
    estoque INT NOT NULL,
    status ENUM('ativo','inativo'),
    id_categoria INT,
    FOREIGN KEY (id_categoria) REFERENCES categoria(id_categoria)
);

CREATE TABLE pedido (
    id_pedido INT AUTO_INCREMENT PRIMARY KEY,
    data_pedido DATETIME DEFAULT CURRENT_TIMESTAMP,
    status ENUM('aberto','pago','enviado','cancelado'),
    valor_total DECIMAL(10,2),
    id_cliente INT,
    id_endereco INT,
    FOREIGN KEY (id_cliente) REFERENCES cliente(id_cliente),
    FOREIGN KEY (id_endereco) REFERENCES endereco(id_endereco)
);

CREATE TABLE item_pedido (
    id_item_pedido INT AUTO_INCREMENT PRIMARY KEY,
    quantidade INT NOT NULL,
    preco_unitario DECIMAL(10,2) NOT NULL,
    id_pedido INT,
    id_produto INT,
    FOREIGN KEY (id_pedido) REFERENCES pedido(id_pedido),
    FOREIGN KEY (id_produto) REFERENCES produto(id_produto)
);

CREATE TABLE pagamento (
    id_pagamento INT AUTO_INCREMENT PRIMARY KEY,
    tipo_pagamento ENUM('cartao','pix','boleto'),
    status_pagamento ENUM('pendente','aprovado','recusado'),
    valor DECIMAL(10,2),
    data_pagamento DATETIME,
    id_pedido INT,
    FOREIGN KEY (id_pedido) REFERENCES pedido(id_pedido)
);


CREATE TABLE entrega (
    id_entrega INT AUTO_INCREMENT PRIMARY KEY,
    transportadora VARCHAR(50),
    codigo_rastreio VARCHAR(50),
    status_entrega ENUM('preparando','enviado','entregue'),
    data_envio DATETIME,
    data_entrega DATETIME,
    id_pedido INT,
    FOREIGN KEY (id_pedido) REFERENCES pedido(id_pedido)
);

INSERT INTO cliente (nome, email, senha, cpf, telefone) VALUES
('Ana Silva', 'ana@email.com', '123456', '111.111.111-11', '11999990001'),
('Bruno Costa', 'bruno@email.com', '123456', '222.222.222-22', '11999990002'),
('Carlos Lima', 'carlos@email.com', '123456', '333.333.333-33', '11999990003');

INSERT INTO endereco (logradouro, numero, bairro, cidade, estado, cep, tipo, id_cliente) VALUES
('Rua A', '100', 'Centro', 'São Paulo', 'SP', '01000-000', 'entrega', 1),
('Rua B', '200', 'Centro', 'Campinas', 'SP', '13000-000', 'entrega', 2),
('Rua C', '300', 'Bairro Novo', 'Rio de Janeiro', 'RJ', '20000-000', 'entrega', 3);

INSERT INTO categoria (nome, descricao) VALUES
('Eletrônicos', 'Produtos eletrônicos em geral'),
('Informática', 'Computadores e acessórios'),
('Livros', 'Livros físicos');

INSERT INTO produto (nome, descricao, preco, estoque, status, id_categoria) VALUES
('Notebook Dell', 'Notebook i5 16GB', 4500.00, 10, 'ativo', 2),
('Mouse Gamer', 'Mouse RGB', 150.00, 50, 'ativo', 2),
('Smartphone Samsung', 'Galaxy S', 3200.00, 15, 'ativo', 1),
('Livro SQL', 'Aprenda SQL do zero', 90.00, 100, 'ativo', 3);

INSERT INTO pedido (status, valor_total, id_cliente, id_endereco) VALUES
('pago', 4650.00, 1, 1),
('pago', 3200.00, 2, 2);

INSERT INTO item_pedido (quantidade, preco_unitario, id_pedido, id_produto) VALUES
(1, 4500.00, 1, 1),
(1, 150.00, 1, 2),
(1, 3200.00, 2, 3);

INSERT INTO pagamento (tipo_pagamento, status_pagamento, valor, data_pagamento, id_pedido) VALUES
('cartao', 'aprovado', 4650.00, NOW(), 1),
('pix', 'aprovado', 3200.00, NOW(), 2);

INSERT INTO entrega (transportadora, codigo_rastreio, status_entrega, data_envio, id_pedido) VALUES
('Correios', 'BR123456', 'enviado', NOW(), 1),
('Jadlog', 'JD654321', 'preparando', NOW(), 2);

---

Exercícios

BLOCO 1: SELECT (BÁSICO)

1. Liste todos os clientes cadastrados.

2. Exiba apenas nome e email dos clientes.

3. Liste todos os produtos ativos.

4. Liste os produtos com preço maior que R$ 500,00.

5. Liste os pedidos com status 'pago'.

6. Exiba todos os pedidos ordenados pela data do pedido (mais recente primeiro).

7. Liste os produtos cujo nome contenha a palavra 'Notebook'.

---

BLOCO 2: SELECT COM JOIN

8. Liste o nome do cliente e o status de seus pedidos.

9. Liste os pedidos com o nome do cliente e a cidade de entrega.

10. Liste os produtos comprados em cada pedido.

11. Liste o nome do produto, a quantidade e o valor total do item (quantidade × preço).

12. Liste os pedidos com o valor total e o tipo de pagamento.

13. Liste o código de rastreio e o nome do cliente.

14. Liste os clientes que já realizaram pedidos.

---

BLOCO 3: FUNÇÕES SQL

15. Exiba o total de clientes cadastrados.

16. Exiba o valor médio dos produtos.

17. Exiba o produto mais caro.

18. Exiba o produto mais barato.

19. Exiba o valor total vendido pela loja.

20. Exiba o total vendido por cliente.

21. Liste os pedidos mostrando o ano e mês da data do pedido.

22. Exiba o nome dos clientes em letras maiúsculas.

23. Exiba os produtos com o preço arredondado.

24. Exiba quantos pedidos existem por status.

---

BLOCO 4: INSERT

25. Insira um novo cliente fictício.

26. Insira um endereço para esse cliente.

27. Cadastre uma nova categoria chamada "Acessórios".

28. Cadastre dois produtos nessa categoria.

29. Crie um novo pedido para esse cliente.

30. Insira os itens desse pedido.

31. Registre o pagamento desse pedido.

32. Registre a entrega desse pedido.

---

BLOCO 5: UPDATE

33. Atualize o telefone de um cliente.

34. Altere o status de um produto para inativo.

35. Atualize o estoque de um produto após uma venda.

36. Atualize o status de um pedido para 'enviado'.

37. Atualize o status da entrega para 'entregue'.

38. Atualize a data de entrega para a data atual.

39. Aplique um aumento de 10% no preço de todos os produtos da categoria Informática.

40. Atualize o status de pagamento para 'recusado' de um pedido específico.

---

DESAFIOS (OPCIONAL)

41. Liste os produtos que nunca foram vendidos.

42. Liste os clientes que nunca fizeram pedidos.

43. Exiba o produto mais vendido (em quantidade).

44. Exiba o cliente que mais gastou.

45. Liste os pedidos cujo valor total seja maior que a média dos pedidos.